package com.dmgis.qxfw.dataservice.commonservice.dmgis_pg_srv.controller;


import com.alibaba.fastjson.JSONObject;

import com.dmgis.qxfw.dataservice.businessservice.common.util.JsonUtil;
import com.dmgis.qxfw.dataservice.commonservice.dmgis_pg_srv.domain.*;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map.Entry;

public class Handle {

    /**
     * 等到查询语句
     *
     * @param
     * @param tableName
     * @return
     */
    public String getData(String param, String tableName) {
        RequestDataBySelect selectParam = null;
        if (param != null && param.length() > 0) {
            selectParam = JSONObject.parseObject(param, RequestDataBySelect.class);
        }
        StringBuilder sql = new StringBuilder();
        String groupStr = " group by ";//group条件
        String orderStr = " order by ";
        String havingStr = "";
        sql.append("select");
        int count = 0;
        int groupCount = 0;
        String[] columns = null;
        String[] sort = null;
        String queryKey = null;
        int limit = 0;
        if (selectParam != null) {
            columns = selectParam.getColumns();
            sort = selectParam.getSort();
            queryKey = selectParam.getParam();
            limit = selectParam.getLimit();
            havingStr = selectParam.getHaving();
        }
        if (columns == null || columns.length == 0) {
            sql.append("*");
        } else {
            for (String column : columns) {
                sql.append(" " + column + ", ");
                if (column.contains("max(") || column.contains("min(") ||
                        column.contains("avg(") || column.contains("sum(") || column.contains("count(")) {
                    count++;
                } else {
                    groupCount++;
                    if (column.contains(" as ")) {
                        groupStr += column.substring(0, column.indexOf(" as ")) + ",";
                    } else if (column.startsWith("distinct ")) {
                        groupStr += column.replace("distinct ", "") + ",";
                    } else {
                        groupStr += column + ",";
                    }
                }
            }
            if (sql.toString().trim().endsWith(",")) {
                String strSql = sql.toString().trim().substring(0, sql.toString().trim().length() - 1);
                sql = new StringBuilder(strSql);
            }
        }
        sql.append(" from " + tableName);
        //where 字段
        if (queryKey != null && queryKey.length() > 0) {
            sql.append(" where " + queryKey);
        }
        //group 字段
        if (count > 0 && groupCount > 0) {
            if (groupStr.endsWith(",")) {
                if (groupStr.contains("distinct"))
                    groupStr = groupStr.replace("distinct", "");
                groupStr = groupStr.substring(0, groupStr.length() - 1);
            }
            sql.append(groupStr);
        }
        if (havingStr != null && !havingStr.equals("")) {
            sql.append(" having " + havingStr);
        }
        //order 字段
        if (sort != null && sort.length > 0) {
            for (String order : sort) {
                orderStr += order + ",";
            }
            if (orderStr.endsWith(",")) {
                orderStr = orderStr.substring(0, orderStr.length() - 1);
            }
            sql.append(orderStr);
        }

        //limit字段
        if (limit > 0) {
            sql.append(" limit " + limit);
        }
        return sql.toString();
    }

    /**
     * 得到删除语句
     *
     * @param
     * @param tableName
     * @return
     * @throws Exception
     */
    public String delData(String param, String tableName) throws Exception {
        StringBuilder sql = new StringBuilder();
        RequestDataByDelete deleteParam = new RequestDataByDelete();
        if (param != null && param.length() > 0) {
            deleteParam = JsonUtil.jsonStr2Object(param, RequestDataByDelete.class);
        } else {
            throw new Exception("删除参数为空！");
        }
        String queryKey = deleteParam.getParam();
        //添加where字段
        if (queryKey != null && queryKey.length() > 0) {
            sql.append("delete from " + tableName);
            sql.append(" where " + queryKey + ";");
        }
        return sql.toString();
    }

    /**
     * 等到更新语句
     *
     * @param
     * @param tableName
     * @return
     * @throws Exception
     */
    public String updateData(String param, String tableName) throws Exception {
        StringBuilder sql = new StringBuilder();
        sql.append("update " + tableName);
        RequestDataByUpdate updateParam = new RequestDataByUpdate();
        if (param != null && param.length() > 0) {
            updateParam = JSONObject.parseObject(param, RequestDataByUpdate.class);
        } else {
            throw new Exception("更新参数为空！");
        }
        String queryKey = updateParam.getParam();
        HashMap<String, Object> map2 = updateParam.getData();
        //获取set字符串
        sql.append(getSetString(map2));
        //添加where字段
        if (queryKey != null && queryKey.length() > 0) {
            sql.append(" where " + queryKey);
        }
        return sql.toString();
    }

    /**
     * 得到多条更新语句
     *
     * @param
     * @param tableName
     * @return
     * @throws Exception
     */
    public String updatesData(String param, String tableName) throws Exception {
        RequestDataByUpdates updateParams = new RequestDataByUpdates();
        if (param != null && param.length() > 0) {
            updateParams = JSONObject.parseObject(param, RequestDataByUpdates.class);
        } else {
            throw new Exception("更新参数为空！");
        }
        List<String> queryKeys = updateParams.getParam();
        List<HashMap<String, Object>> maps = updateParams.getData();
        StringBuilder sqls = new StringBuilder();
        for (int i=0;i<queryKeys.size();i++) {
            HashMap<String,Object> map2=maps.get(i);
            String queryKey=queryKeys.get(i);
            sqls.append("update " + tableName);
            //获取set字符串
            sqls.append(getSetString(map2));
            //添加where字段
            if (queryKey != null && queryKey.length() > 0) {
                sqls.append(" where " + queryKey);
            }
            sqls.append(";");
        }
        return sqls.toString();
    }

    /**
     * 得到插入语句
     *
     * @param
     * @param tableName
     * @return 第一个值为sql语句，第二个值为是否返回标识字段
     * @throws Exception
     */
    public String[] insertData(String param, String tableName) throws Exception {
        StringBuilder sql = new StringBuilder();
        RequestDataByInsert insertParam = new RequestDataByInsert();
        if (param != null && param.length() > 0) {
            insertParam = JSONObject.parseObject(param, RequestDataByInsert.class);
        } else {
            throw new Exception("插入参数为空");
        }
        List<HashMap<String, Object>> data = insertParam.getData();
        //获取insert字符串
        sql.append(getInsertString(data, tableName));
        String[] result = new String[2];
        result[0] = sql.toString();
        result[1] = insertParam.getIsReturning();
        return result;
    }

    /**
     * 拼接set字段
     *
     * @param map
     * @return
     */
    private String getSetString(HashMap<String, Object> map) {
        String str = " set";
        if (map != null) {
            for (Entry<String, Object> entry : map.entrySet()) {
                if (entry.getValue() != null && entry.getValue().toString().length() > 0) {
                    if (entry.getValue().getClass().equals(String.class)) {
                        str += " " + entry.getKey() + "='" + entry.getValue().toString().trim() + "',";
                    } else {
                        str += " " + entry.getKey() + "=" + entry.getValue() + ",";
                    }
                } else {
                    continue;
                }
            }
        }
        if (str.endsWith(",")) {
            str = str.substring(0, str.length() - 1);
        }
        return str;
    }

    /**
     * 拼接insert字段
     *
     * @param data
     * @param tableName
     * @return
     */
    private String getInsertString(List<HashMap<String, Object>> data, String tableName) {
        StringBuilder strInsert = new StringBuilder();
        if (data != null && data.size() > 0) {
            for (HashMap<String, Object> map : data) {
                strInsert.append("insert into " + tableName);
                String columns = " (";
                String values = " values(";
                for (Entry<String, Object> entry : map.entrySet()) {
                    if (entry.getValue() != null && entry.getValue().toString().length() > 0) {
                        columns += entry.getKey() + ",";
                        if (entry.getValue().getClass().equals(String.class) ||
                                entry.getValue().getClass().equals(Date.class)) {
                            values += "'" + entry.getValue() + "',";
                        } else {
                            values += entry.getValue() + ",";
                        }
                    }
                }
                if (columns.trim().endsWith(",")) {
                    columns = columns.trim().substring(0, columns.length() - 2) + ")";
                }
                if (values.trim().endsWith(",")) {
                    values = values.trim().substring(0, values.length() - 2) + ");";
                }
                strInsert.append(columns + values);
            }
        }
        return strInsert.toString();
    }
}
